﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SysFramework;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BLL.Statistics
{
    /// <summary>
    /// ERP数据(U8)
    /// </summary>
    internal class ERPDatas:SysFramework.PubMethod
    {
       OutBaseHelper cdb = new OutBaseHelper();
        /// <summary>
        /// 销售发票数据
        /// </summary>
        /// <returns></returns>
        public DataTable InvoiceData(string dateType, string baseCode,string condition,bool isPass,ref string errMsg)
        {
            DataTable table = new DataTable();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(c.iNatSum) money,f.cPersonCode userCode,u.cPersonName userName,u.cDepCode dept");
                sb.Append(" from SaleBillVouchs c inner join SaleBillVouch f on c.SBVID =f.SBVID ");
                sb.Append(" inner join Person u on u.cPersonCode=f.cPersonCode");
                sb.Append(" where  f.cInvalider is null ");
                if (dateType == "quarterQuery")
                {
                    #region 季度查询
                    if (isPass)
                    {
                        if (DateTime.Now.Month < 4)
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year + " and month(dDate) in (10,11,12)");
                        }
                        else
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate) in (" + getQuarter(DateTime.Now.Month, true) + ")");
                        }
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate) in (" + getQuarter(DateTime.Now.Month) + ")");
                    }
                    #endregion
                }
                else if (dateType == "yearQuery")
                {
                    #region 年度查询
                    if (isPass)
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year);
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year);
                    }
                    #endregion
                }
                else
                {
                    #region 月度查询
                    if (isPass)
                    {
                        if (DateTime.Now.Month == 1)
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year + " and month(dDate) =12");
                        }
                        else
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate)=" + DateTime.Now.AddMonths(-1).Month);
                        }
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate)=" + DateTime.Now.Month);
                    }
                    #endregion
                }
                sb.Append(" group by f.cPersonCode,u.cPersonName,u.cDepCode");
                table = cdb.getData(sb.ToString(), baseCode);
                return table;
            }
            catch (Exception ex)
            { 
            
            }
            return table;
        }
        /// <summary>
        /// 销售发票数据(U8初始)
        /// </summary>
        /// <param name="dateType">日期类型</param>
        /// <param name="baseCode">账套编码</param>
        /// <param name="condition">检索条件</param>
        /// <param name="isPass">往期查询</param>
        /// <param name="errMsg">错误代码</param>
        /// <returns></returns>
        public DataTable SaleBills(string dateType, string baseCode, string condition, bool isPass, ref string errMsg)
        {
            DataTable table = new DataTable();
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select c.iNatSum money,f.cPersonCode userCode,u.cPersonName userName,");
                sb.Append("u.cDepCode dept,f.cCusCode cuCode,cu.cCusName cuName,c.cInvCode proCode,c.cInvName proName");
                sb.Append(" from SaleBillVouchs c inner join SaleBillVouch f on c.SBVID =f.SBVID ");
                sb.Append(" inner join Person u on u.cPersonCode=f.cPersonCode");
                sb.Append(" inner join customer cu on cu.cCusCode=f.cCusCode");
                sb.Append(" where  f.cInvalider is null ");
                if (dateType == "quarterQuery")
                {
                    #region 季度查询
                    if (isPass)
                    {
                        if (DateTime.Now.Month < 4)
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year + " and month(dDate) in (10,11,12)");
                        }
                        else
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate) in (" + getQuarter(DateTime.Now.Month, true) + ")");
                        }
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate) in (" + getQuarter(DateTime.Now.Month) + ")");
                    }
                    #endregion
                }
                else if (dateType == "yearQuery")
                {
                    #region 年度查询
                    if (isPass)
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year);
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year);
                    }
                    #endregion
                }
                else
                {
                    #region 月度查询
                    if (isPass)
                    {
                        if (DateTime.Now.Month == 1)
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.AddYears(-1).Year + " and month(dDate) =12");
                        }
                        else
                        {
                            sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate)=" + DateTime.Now.AddMonths(-1).Month);
                        }
                    }
                    else
                    {
                        sb.Append(" and year(dDate)=" + DateTime.Now.Year + " and month(dDate)=" + DateTime.Now.Month);
                    }
                    #endregion
                }
                //sb.Append(" group by f.cPersonCode,u.cPersonName,u.cDepCode");
                table = cdb.getData(sb.ToString(), baseCode);
                return table;
            }
            catch (Exception ex)
            {
                errMsg = ex.Message;
            }
            return table;
        }
        /// <summary>
        /// 销售订单存货
        /// </summary>
        /// <param name="dataType"></param>
        /// <param name="baseCode"></param>
        /// <param name="condition"></param>
        /// <param name="errMsg"></param>
        /// <returns></returns>
        public DataTable SaleOrderProduct(string baseCode, string condition, ref string errMsg)
        {
            DataTable table = new DataTable();
            try
            {
               
                StringBuilder sb = new StringBuilder();
                sb.Append("select s.cSOCode orderCode,s.cInvCode proCode,s.cInvName proName, convert(decimal(18,2),s.iQuantity) number ");
                sb.Append(",m.dDate hDate");
                sb.Append(" from SO_SODetails s inner join SO_SOMain m on m.cSOCode=s.cSOCode where m.iStatus=1 and cCloser is null");
                if (!string.IsNullOrWhiteSpace(condition))
                {
                    var obj = JsonConvert.DeserializeObject<JObject>(condition);
                    string sDate = getNowDate(-5);
                    if (obj.Property("sDate") != null && !string.IsNullOrWhiteSpace(obj["sDate"].ToString()))
                    {
                        sDate = obj["sDate"].ToString();
                    }
                    string eDate = getNowDate();
                    if (obj.Property("eDate") != null && !string.IsNullOrWhiteSpace(obj["eDate"].ToString()))
                    {
                        eDate = obj["eDate"].ToString();
                    }
                    sb.Append(" and m.dDate between '"+sDate+"' and '"+eDate+"'");
                }
                table = cdb.getData(sb.ToString(), baseCode);//只获取审核通过且未管理的销售订单数据信息
            }
            catch (Exception ex)
            { 
                errMsg=ex.Message;
            }
            return table;
            
        }
        /// <summary>
        /// 获取季度所属月份
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private string getQuarter(int? month, bool isPass = false)
        {
            if (month == null)
            {
                month = DateTime.Now.Month;
            }
            else if (month < 1)
            {
                return "10,11,12";
            }
            if (isPass)
            {
                month = month - 3;
                return this.getQuarter(month, false);
            }
            else
            {
                if (month < 4)
                {
                    return "1,2,3";
                }
                else if (month > 3 && month < 7)
                {
                    return "4,5,6";
                }
                else if (month > 6 && month < 10)
                {
                    return "7,8,9";
                }
                else
                {
                    return "10,11,12";
                }
            }



        }
    }
}
